﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using University.Workshop.Model;
using System.Data.SqlClient;
using System.Collections.ObjectModel;
using System.Globalization;

namespace University.Workshop.DataAccess
{
    public static class WatchListDataAccess
    {
        private const string ConnectionString = "Data Source=.\\sqlexpress; Initial Catalog=LQSdataBase; Integrated Security=true";
        public static void ModifyComment(long userId, long productId, string note)//el producto ya ha sido agregado a la lista, se agrega una nota
        {
            SqlConnection connection = null;
            connection = new SqlConnection(ConnectionString);
            try
            {
                SqlCommand command = connection.CreateCommand();
                command.CommandText = "UPDATE WATCHLIST SET Comment=@note WHERE UserId=@userId and ProductId=@productId";
                command.Parameters.AddWithValue("UserId", userId);
                command.Parameters.AddWithValue("ProductId", productId);
                command.Parameters.AddWithValue("note", note);
                connection.Open();
                int result = command.ExecuteNonQuery();
                if (result <= 0)
                {
                    throw new DataAccessException("Error modifying a comment");
                }
            }
            catch (SqlException ex)
            { throw new DataAccessException("Error modifying a product", ex); }
            finally
            {
                if (connection != null)
                {
                    connection.Close();
                }
                SqlConnection.ClearAllPools();
            }
        }
        public static void DeleteProduct(long userId, long productId)
        {
            SqlConnection connection = null;
            try
            {
                connection = new SqlConnection(ConnectionString);
                SqlCommand cmd = connection.CreateCommand();
                cmd.CommandText = "delete from WatchList where UserId=@userId and ProductId=@productId";
                cmd.Parameters.AddWithValue("userId", userId);
                cmd.Parameters.AddWithValue("productId", productId);
                connection.Open();
                int result = cmd.ExecuteNonQuery();
                if (result <= 0)
                {
                    throw new DataAccessException("Error deleting a product");
                }

            }
            catch (SqlException ex)
            {

                throw new DataAccessException("Error deleting a product", ex);
            }
            finally
            {
                if (connection != null)
                {
                    connection.Close();
                }
                SqlConnection.ClearAllPools();
            }
        }
        public static Collection<WatchListItem> GetList(long userId)
        {
            Collection<WatchListItem> list = new Collection<WatchListItem>();
            SqlConnection connection = null;
            connection = new SqlConnection(ConnectionString);
            try
            {
                SqlCommand command = connection.CreateCommand();
                command.CommandText = "SELECT * FROM WatchList WHERE UserId=@userId";
                command.Parameters.AddWithValue("userId", userId);
                connection.Open();
                SqlDataReader reader = command.ExecuteReader();
                if (!reader.HasRows)
                {
                    return null;
                }
                while (reader.Read())
                {
                    WatchListItem item = new WatchListItem();
                    Product product = new Product();
                    long id = Int64.Parse(reader["ProductId"].ToString(), CultureInfo.CurrentCulture);
                    product = ProductDataAccess.GetProductById(id);
                    item.RelatedProduct = product;
                    item.Comment = reader["Comment"].ToString();
                    item.UserId = userId;
                    list.Add(item);
                }
            }
            catch (SqlException ex)
            {
                throw new DataAccessException("Error getting a product", ex);
            }
            finally
            {
                if (connection != null)
                {
                    connection.Close();
                }
                SqlConnection.ClearAllPools();
            }
            return list;
        }
        public static void InsertProduct(long userId, long productId, string comment)//agrega a la lista
        {
            SqlConnection connection = null;
            try
            {
                connection = new SqlConnection(ConnectionString);
                SqlCommand cmd = connection.CreateCommand();
                cmd.CommandText = "INSERT INTO WATCHLIST(UserId, ProductId, Comment) VALUES (@userId, @productId,@comment)";
                cmd.Parameters.AddWithValue("userId",userId);
                cmd.Parameters.AddWithValue("productId", productId);
                cmd.Parameters.AddWithValue("comment", comment);
                connection.Open();
                int result = cmd.ExecuteNonQuery();
                if (result <= 0)
                {
                    throw new DataAccessException("Error inserting a product");
                }

            }
            catch (SqlException ex)
            {

                throw new DataAccessException("Error inserting a product", ex);
            }
            finally
            {
                if (connection != null)
                {
                    connection.Close();
                }
                SqlConnection.ClearAllPools();
            }

        }

    }
}
